﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for DAL_Accounts
/// </summary>
public class DAL_Accounts
{
    DbProvider db = new DbProvider();

    public void Accounts_Insert(DTO_Accounts obj)
    {

        string sql = string.Format("INSERT INTO [Accounts] VALUES (N'{0}',N'{1}',N'{2}','{3}','{4}',N'{5}',N'{6}',N'{7}',N'{8}','{9}')", obj.UserName, obj.PassWord, obj.FullName, obj.BirthDay, obj.Sex, obj.Province_Id, obj.Address, obj.Tel, obj.Mail, obj.Status);

        try
        {
            db.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }

    }
    public void Accounts_UpdateByAll(string Id, DTO_Accounts obj)
    {

        //string sql = string.Format("UPDATE [Accounts] SET [UserName] = '{0}',PassWord = '{1}',[FullName] = '{2}',[BirthDay] = '{3}',[Sex] = {4}',[Province_Id] = '{5}',[Address] = {6}',[Tel] = '{7}',[Mail] = '{8}',[Status] = '{9}' WHERE [Id] = '{10}'", obj.UserName, obj.PassWord, obj.FullName, obj.BirthDay, obj.Sex, obj.Province_Id, obj.Address, obj.Tel, obj.Mail, obj.Status, Id);
        string sql1 = "UPDATE [Accounts] SET";
        if (obj.UserName !=null)
        {
            sql1 += " UserName = '" + obj.UserName + "',";
        }
        if (obj.PassWord != null) sql1 += " PassWord = N'" + obj.PassWord + "',";
        if (obj.FullName != null) sql1 += " FullName = N'" + obj.FullName + "',";
        if (obj.BirthDay != null) sql1 += " BirthDay = '" + obj.BirthDay + "',";
        sql1 += " Sex = '" + obj.Sex + "',";
        if (obj.Province_Id != 0) sql1 += " Province_Id = '" + obj.Province_Id + "',";
        if (obj.Address != null) sql1 += " Address = N'" + obj.Address + "',";
        if (obj.Tel != null) sql1 += " Tel = '" + obj.Tel + "',";
        if (obj.Mail != null) sql1 += " Mail = '" + obj.Mail + "',";
        sql1+=" [Status] = '"+obj.Status+"' where Id = '"+Id+"'";
        try
        {
            db.ExecuteNonQuery(sql1);
        }
        catch (Exception)
        {

            throw;
        }
        //return sql1;

    }

    public void Accounts_UpdateBySet(string Id, string Set)
    {

        string sql = string.Format("UPDATE [Accounts] SET {0} WHERE [Id] = '{1}'", Set, Id);

        try
        {
            db.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }

    }
    public void Accounts_Delete(string Id)
    {

        string sql = string.Format("DELETE FROM Accounts WHERE Id = '{0}'", Id);

        try
        {
            db.ExecuteNonQuery(sql);
        }
        catch (Exception)
        {

            throw;
        }

    }

    public DataTable Accounts_GetByAll()
    {
        DataTable dt = new DataTable();
        string sql = string.Format("select * from Accounts");
        try
        {
            dt = db.ExecuteQuery(sql);

        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Accounts_GetByTop(int top, string where, string order)
    {
        DataTable dt = new DataTable();
        //string sql = string.Format("select top {0} * from Product where = '{1}' Order by [{2}] DESC,[Order]", top, where,order);
        string sql = "";
        if (top <= 0)
        {
            sql += "SELECT * FROM Accounts";
        }
        else
        {
            sql += "SELECT TOP " + top + " * FROM Accounts";
        }
        if (where != "")
        {
            sql += " WHERE " + where;
        }
        if (order != "")
        {
            sql += " Order by " + order;
        }
        try
        {
            dt = db.ExecuteQuery(sql);

        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
    public DataTable Accounts_GetByID(string id)
    {
        DataTable dt = new DataTable();
        string sql = string.Format("select * from Accounts where Id = {0}", id);
        try
        {
            dt = db.ExecuteQuery(sql);

        }
        catch (Exception)
        {

            throw;
        }
        return dt;
    }
}